import com.google.common.collect.Lists;
import com.idea.entity.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;

/**
 * @author lajiaochaorou
 * ClassName excelTest
 * @data 2022/12/6  13:45
 **/
public class ContractExcelDemo {

    String path = "F:\\OSC_Project\\demo";

    // 左侧标题列的开始下标
    static int leftTitleColBeginIndex = 0;
    // 左侧标题列的截止下标
    static int leftTitleColEndIndex = 1;

    // 右侧数据集的开始列下标
    static int rightValueColBeginIndex = leftTitleColEndIndex + 1;


    public ContractDTO initData() {
        //1.组装合同
        ContractDTO ct = new ContractDTO();
        ct.setId("contractI001");
        ct.setCurrency("USC");
        ct.setCustomerName("SHIPPING LINES CO., LTD.");
        ct.setContractName("SHIPPING LINES CO., LTD.");
        ct.setLeaseType("Long Term Lease");
        ct.setEquipmentCondition("New Production");
        ct.setEffectiveDate(new Date());
        ct.setEnndBuildUpPeriod(new Date());
        ct.setBeginBuildUpPeriod(new Date());

        //2.地区服务
        //箱型号 D20
        PickUpDTO pick1 = new PickUpDTO("01", "Abidjan", 10000);
        PickUpDTO pick2 = new PickUpDTO("02", "Bahrain", 10000);
        List<PickUpDTO> pickUp1 = Lists.newArrayList(pick1,pick2);
        //R4
        PickUpDTO pick3 = new PickUpDTO("03", "Bahrain", 500);
        PickUpDTO pick4 = new PickUpDTO("04", "Barranco", 500);

        List<PickUpDTO> pickUp2 = Lists.newArrayList(pick3,pick4);

        //D40
        PickUpDTO pick5 = new PickUpDTO("05", "Ad Dammam", 500);
        PickUpDTO pick6 = new PickUpDTO("06", "Adelaide", 5300);
        PickUpDTO pick7 = new PickUpDTO("07", "Calcutta", 1300);
        List<PickUpDTO> pickUp3 = Lists.newArrayList(pick5,pick6);

        //------------------------3.组装箱型
        ContainerDTO cte = new ContainerDTO();
        cte.setId("1");
        cte.setEquipmentType("D20' Dry Container");
        cte.setEquipmentQuantity("2000");
        cte.setPickUpDTOS(pickUp1);


        ContainerDTO cte2 = new ContainerDTO();
        cte2.setId("2");
        cte2.setEquipmentType("D40 Truck Chassis");
        cte2.setEquipmentQuantity("1000");
        cte2.setPickUpDTOS(pickUp2);



        ContainerDTO cte3 = new ContainerDTO();
        cte3.setId("3");
        cte3.setEquipmentType("R4H' Reefer High-Cube");
        cte3.setEquipmentQuantity("2000");
        cte3.setPickUpDTOS(pickUp3);




        //------------------5.组装不同地点下的提箱报价
        //20箱型下的地点-上海-沈阳-黄埔
        PriceDTO shPrice1 = new PriceDTO("1", "Rate", 20);
        PriceDTO shPrice2 = new PriceDTO("2", "1-1000TEU:", 20);
        PriceDTO shPrice3 = new PriceDTO("3", "1000-2000TEU:", 20);
        List<PriceDTO> price_sh1 = Lists.newArrayList(shPrice1, shPrice2, shPrice3);

        PriceDTO sYPrice1 = new PriceDTO("4", "1-1000 units", 80);
        PriceDTO sYPrice2 = new PriceDTO("5", "1001-1500 units:", 90);
        PriceDTO sYPrice3 = new PriceDTO("6", "1501-2000 units:", 20);
        List<PriceDTO> price_sy1 = Lists.newArrayList(sYPrice1, sYPrice2, sYPrice3);


        PriceDTO hpPrice1 = new PriceDTO("7", "1-1825 days", 20);
        PriceDTO hpPrice2 = new PriceDTO("8", "1826-3650 days:", 18);
        List<PriceDTO> price_hp1 = Lists.newArrayList(hpPrice1, hpPrice2);

        //40箱型下的地点-global -黄埔
        PriceDTO gbPrice1 = new PriceDTO("9", "1-1825 days", 20);
        PriceDTO gbPrice2 = new PriceDTO("10", "1826-3650 days:", 18);
        List<PriceDTO> price_gb1 = Lists.newArrayList(gbPrice1, gbPrice2);

        PriceDTO hpPrice1_4 = new PriceDTO("11", "1-1825 days", 20);
        PriceDTO hpPrice2_4 = new PriceDTO("12", "1826-3650 days:", 18);
        List<PriceDTO> hp_price_gb1 = Lists.newArrayList(hpPrice1_4, hpPrice2_4);

        //40-r箱型b globPrice1
        PriceDTO globPrice1 = new PriceDTO("13", "1-1000 units", 80);
        PriceDTO globPrice2 = new PriceDTO("14", "1001-1500 units:", 78);
        PriceDTO globPrice3 = new PriceDTO("15", "1501-2000 units:", 75);
        List<PriceDTO> r4_price_gb1 = Lists.newArrayList(globPrice1, globPrice2, globPrice3);

        //---------------4.组装地区下的不同提箱地点
        //20箱型
        LocationDTO lt1_20 = new LocationDTO("1", "上海", "EO Addon", price_sh1);
        LocationDTO lt2_20 = new LocationDTO("2", "沈阳", "Vol Discnt", price_sy1);
        LocationDTO lt3_20 = new LocationDTO("3", "黄埔", "Time Step", price_hp1);
        List<LocationDTO> locationD20 = Lists.newArrayList(lt1_20, lt2_20, lt3_20);
        cte.setLocations(locationD20);

        //40箱型
        LocationDTO lt1_40 = new LocationDTO("4", "global", "Time Step", price_gb1);
        LocationDTO lt2_40 = new LocationDTO("5", "黄埔", "Time Step", hp_price_gb1);
        List<LocationDTO> locationD40 = Lists.newArrayList(lt1_40, lt2_40);
        cte2.setLocations(locationD40);

        //40-R箱型
        LocationDTO lt1_40R = new LocationDTO("6", "global", "Vol Discnt", r4_price_gb1);
        List<LocationDTO> locationR40 = Lists.newArrayList(lt1_40R);
        cte3.setLocations(locationR40);

        //组装集装箱下的数据
        ct.getContainers().add(cte);
        ct.getContainers().add(cte2);
        ct.getContainers().add(cte3);




        //总箱型数量
        List<LocationDTO> count = Lists.newArrayList();
        count.addAll(locationD20);
        count.addAll(locationD40);
        count.addAll(locationR40);
        return ct;
    }



    /**
     * 生成 Customer Name 行
     *
     * @param sheet
     * @param style
     * @param data
     */
    private void createCustomerNameRow(XSSFSheet sheet, XSSFCellStyle style, ContractDTO data) {
        Row row = sheet.createRow(0);
        Cell cell1 = row.createCell(0);
        CellRangeAddress ads1 = new CellRangeAddress(0, 0, leftTitleColBeginIndex, leftTitleColEndIndex);
        sheet.addMergedRegion(ads1);
        cell1.setCellValue("Customer Name");
        cell1.setCellStyle(style);
        Cell cell2 = row.createCell(rightValueColBeginIndex);
        CellRangeAddress ads2 = new CellRangeAddress(0, 0, rightValueColBeginIndex, data.getColMaxEndIndex());
        sheet.addMergedRegion(ads2);
        cell2.setCellValue(data.getCustomerName());
        cell2.setCellStyle(style);
    }

    /**
     * 生成 Lease Type 行
     *
     * @param sheet
     * @param style
     * @param data
     */
    private void createLeaseRow(XSSFSheet sheet, XSSFCellStyle style, ContractDTO data) {
        int rowIndex = 1;
        Row row = sheet.createRow(rowIndex);
        Cell cell1 = row.createCell(0);
        CellRangeAddress ads1 = new CellRangeAddress(rowIndex, rowIndex, leftTitleColBeginIndex, leftTitleColEndIndex);
        sheet.addMergedRegion(ads1);
        cell1.setCellValue("Lease Type");
        cell1.setCellStyle(style);
        Cell cell2 = row.createCell(ads1.getLastColumn() + 1);
        CellRangeAddress ads2 = new CellRangeAddress(rowIndex, rowIndex, ads1.getLastColumn() + 1, data.getColMaxEndIndex());
        sheet.addMergedRegion(ads2);
        cell2.setCellValue(data.getLeaseType());
        cell2.setCellStyle(style);
    }

    /**
     * 生成 Effective Date 行
     *
     * @param sheet
     * @param style
     * @param data
     */
    private void createEffectiveDateRow(XSSFSheet sheet, XSSFCellStyle style, ContractDTO data) {
        int rowIndex = 2;
        Row row = sheet.createRow(rowIndex);
        Cell cell1 = row.createCell(0);
        CellRangeAddress ads1 = new CellRangeAddress(rowIndex, rowIndex, leftTitleColBeginIndex, leftTitleColEndIndex);
        sheet.addMergedRegion(ads1);
        cell1.setCellValue("Effective Date");
        cell1.setCellStyle(style);
        // 创建From列
        Cell cell2 = row.createCell(ads1.getLastColumn() + 1);
        cell2.setCellValue("From");
        cell2.setCellStyle(style);
        // 创建时间值列
        Cell cell3 = row.createCell(cell2.getColumnIndex() + 1);
        CellRangeAddress ads3 = new CellRangeAddress(rowIndex, rowIndex, cell2.getColumnIndex() + 1, data.getColMaxEndIndex());
        sheet.addMergedRegion(ads3);
        cell3.setCellValue(data.getEffectiveDate());
        cell3.setCellStyle(style);
    }


    /**
     * 生成 Build Up Period 行
     *
     * @param sheet
     * @param style
     * @param data
     */
    private void createBuildUpPeriodRow(XSSFSheet sheet, XSSFCellStyle style, ContractDTO data) {
        int rowIndex = 3;
        Row row = sheet.createRow(rowIndex);
        Cell cell1 = row.createCell(0);
        CellRangeAddress ads1 = new CellRangeAddress(rowIndex, rowIndex, leftTitleColBeginIndex, leftTitleColEndIndex);
        sheet.addMergedRegion(ads1);
        cell1.setCellValue("Build Up Period");
        cell1.setCellStyle(style);
        // 创建From列
        Cell cell2 = row.createCell(ads1.getLastColumn() + 1);
        cell2.setCellValue("From");
        cell2.setCellStyle(style);
        // 创建时间值列
        Cell cell3 = row.createCell(cell2.getColumnIndex() + 1);
        CellRangeAddress ads3 = new CellRangeAddress(rowIndex, rowIndex, cell2.getColumnIndex() + 1, data.getColMaxEndIndex() / 2);
        sheet.addMergedRegion(ads3);
        cell3.setCellValue(data.getBeginBuildUpPeriod());
        cell3.setCellStyle(style);

        // 创建From列
        Cell cell4 = row.createCell(ads3.getLastColumn() + 1);
        cell4.setCellValue("From");
        cell4.setCellStyle(style);
        // 创建时间值列
        Cell cell5 = row.createCell(cell4.getColumnIndex() + 1);
        CellRangeAddress ads4 = new CellRangeAddress(rowIndex, rowIndex, cell4.getColumnIndex() + 1, data.getColMaxEndIndex());
        sheet.addMergedRegion(ads4);
        cell5.setCellValue(data.getEnndBuildUpPeriod());
        cell5.setCellStyle(style);
    }


    /**
     * 生成 Equipment Size / Type 行
     *
     * @param sheet
     * @param style
     * @param data
     */
    private void createEquipmentSizeTypeRow(XSSFSheet sheet, XSSFCellStyle style, ContractDTO data) {
        int rowIndex = 4;
        Row row = sheet.createRow(rowIndex);
        Cell cell1 = row.createCell(0);
        CellRangeAddress ads1 = new CellRangeAddress(rowIndex, rowIndex, leftTitleColBeginIndex, leftTitleColEndIndex);
        sheet.addMergedRegion(ads1);
        cell1.setCellValue("Equipment Size / Type");
        cell1.setCellStyle(style);
        /**
         * 动态根据当前合同的箱类别创建箱型列
         * TODO 遍历性能问题待优化
         */
        Cell cell2 = null;
        CellRangeAddress ads2 = null;
        ContainerDTO containerDTO = null;
        for (int i = 0, len = data.getContainers().size(); i < len; i++) {
            containerDTO = data.getContainers().get(i);
            if (i == 0) {
                cell2 = row.createCell(ads1.getLastColumn() + 1);
                ads2 = new CellRangeAddress(rowIndex, rowIndex, ads1.getLastColumn() + 1, ads1.getLastColumn() + containerDTO.getRightColSumCount());
            } else {
                cell2 = row.createCell(ads2.getLastColumn() + 1);
                ads2 = new CellRangeAddress(rowIndex, rowIndex, ads2.getLastColumn() + 1, ads2.getLastColumn() + containerDTO.getRightColSumCount());
            }
            sheet.addMergedRegion(ads2);
            cell2.setCellValue(containerDTO.getEquipmentType());
            cell2.setCellStyle(style);
        }

    }

    @Test
    public void excelDemo() throws IOException {

        // 初始化测试数据
        ContractDTO data = initData();


        //1.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();

        //2.创一个sheet 工作表
        XSSFSheet sheet = workbook.createSheet();
        // 头部样式
        XSSFCellStyle headStyle = getAndSetXSSFCellStyleHeader(workbook);
        // 左侧标题样式
        XSSFCellStyle leftTitleStyle = getAndSetXSSFCellStyle(workbook);
        // 正文样式
        XSSFCellStyle cellStyle = getAndSetXSSFCellStyle(workbook);

        /**
         * 1> 拼装 Customer Name 行
         */
        this.createCustomerNameRow(sheet, leftTitleStyle, data);

        /**
         * 2> 拼装 Lease Type 行
         */
        this.createLeaseRow(sheet, leftTitleStyle, data);

        /**
         * 3> 拼装 Effective Date 行
         */
        this.createEffectiveDateRow(sheet, leftTitleStyle, data);

        /**
         * 4> 拼装 Build Up Period 行
         */
        this.createBuildUpPeriodRow(sheet, leftTitleStyle, data);

        this.createEquipmentSizeTypeRow(sheet, leftTitleStyle, data);

        this.createPickupLocationRow(sheet, leftTitleStyle, data);


        FileOutputStream out = new FileOutputStream(path + "/合同推荐导出1" + System.currentTimeMillis() + ".xlsx");
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();

    }

    /**
     * 生成 Pick-up Location 行
     *
     * @param sheet
     * @param style
     * @param data
     */
    private void createPickupLocationRow(XSSFSheet sheet, XSSFCellStyle style, ContractDTO data) {
        int rowIndex = 5;

        // 获取当前提箱地点集合大小，作为当前总行数
        int rowMaxCount = data.getPickupMaxRowCount();

        Row row = sheet.createRow(rowIndex);
        Cell cell1 = row.createCell(0);
        CellRangeAddress ads1 = new CellRangeAddress(rowIndex, rowIndex + rowMaxCount - 1, leftTitleColBeginIndex, leftTitleColEndIndex);
        sheet.addMergedRegion(ads1);
        cell1.setCellValue("Pick-up Location");
        cell1.setCellStyle(style);

        /**
         * 组装提箱地点 & 提箱量
         */
        Cell pickupNameCell = null;
        CellRangeAddress pickupNameAds = null;
        Cell pickupValCell = null;
        CellRangeAddress pickupValAds = null;
        ContainerDTO containerDTO = null;
        PickUpDTO pickUpDTO = null;

        // 当前起始列下标
        int beginCol = ads1.getLastColumn() + 1;

        // 遍历出所有箱型
        for (int i = 0, len = data.getContainers().size(); i < len; i++) {
            containerDTO = data.getContainers().get(i);
            // 遍历出每个提箱地点
            for (int j = 0, len2 = containerDTO.getPickupSize(); j < len2; j++) {
                // 提箱地点和提箱数量 起始行 = rowIndex + j
                int beginRow = rowIndex + j;
                pickUpDTO = containerDTO.getPickUpDTOS().get(j);
                // 组装第一行提箱点数据
                if (i == 0) {
                    // 提箱地点
                    pickupNameCell = row.createCell(beginCol);
                    pickupNameAds = new CellRangeAddress(beginRow, beginRow, beginCol, ads1.getLastColumn() + containerDTO.getRightColSumCount() / 2);
                    System.out.println("第"+beginRow+"行地点："+ beginRow +","+ beginRow+"," +beginCol +","+ (ads1.getLastColumn() +containerDTO.getRightColSumCount() / 2));


                    // 提箱数量
                    pickupValCell = row.createCell(pickupNameAds.getLastColumn() + 1);
                    pickupValAds = new CellRangeAddress(beginRow, beginRow, pickupNameAds.getLastColumn() + 1, pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2);
                    System.out.println("第"+beginRow+"行数量："+beginRow +","+ beginRow  +","+ (pickupNameAds.getLastColumn() + 1)  +","+(pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2));

                } else {
                    // 提箱地点
                    pickupNameCell = row.createCell(beginCol);
                    pickupNameAds = new CellRangeAddress(beginRow, beginRow, beginCol, beginCol + containerDTO.getRightColSumCount() / 2 - 1);
                    System.out.println("第"+beginRow+"行地点："+ beginRow +","+beginRow +","+ beginCol  +","+(beginCol + containerDTO.getRightColSumCount() / 2 - 1));


                    // 提箱数量
                    pickupValCell = row.createCell(pickupNameAds.getLastColumn() + 1);
                    pickupValAds = new CellRangeAddress(beginRow, beginRow, pickupNameAds.getLastColumn() + 1, pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2);
                    System.out.println("第"+beginRow+"行数量："+beginRow +","+ beginRow +","+ (pickupNameAds.getLastColumn() + 1)  +","+(pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2));

                }
                sheet.addMergedRegion(pickupNameAds);
                pickupNameCell.setCellValue(pickUpDTO.getRegion());
                pickupNameCell.setCellStyle(style);

                sheet.addMergedRegion(pickupValAds);
                pickupValCell.setCellValue(pickUpDTO.getSize());
                pickupValCell.setCellStyle(style);
            }

            // 记录当前最大的列起始下标
            beginCol += containerDTO.getRightColSumCount();

        }
    }

    private void createPickupLocationRowTest(XSSFSheet sheet, XSSFCellStyle style, ContractDTO data) {
        int rowIndex = 5;

        // 获取当前提箱地点集合大小，作为当前总行数
        int rowMaxCount = data.getPickupMaxRowCount();

        Row row1 = sheet.createRow(rowIndex);
        Cell cell1 = row1.createCell(0);
        CellRangeAddress ads1 = new CellRangeAddress(rowIndex, rowIndex + rowMaxCount - 1, leftTitleColBeginIndex, leftTitleColEndIndex);
        sheet.addMergedRegion(ads1);
        cell1.setCellValue("Pick-up Location");
        cell1.setCellStyle(style);

        /**
         * 组装提箱地点 & 提箱量
         */
        Cell pickupNameCell = null;
        CellRangeAddress pickupNameAds = null;
        Cell pickupValCell = null;
        CellRangeAddress pickupValAds = null;
        ContainerDTO containerDTO = null;
        PickUpDTO pickUpDTO = null;

        // 当前起始列下标
        int beginCol = ads1.getLastColumn() + 1;

        // 遍历出所有箱型
        for (int i = 0, len = data.getContainers().size(); i < len; i++) {
            containerDTO = data.getContainers().get(i);
            // 遍历出每个提箱地点
            for (int j = 0, len2 = containerDTO.getPickupSize(); j < len2; j++) {
                // 提箱地点和提箱数量 起始行 = rowIndex + j
                int beginRow = rowIndex + j;
                //问题行？
                Row row = sheet.createRow(beginRow);
                pickUpDTO = containerDTO.getPickUpDTOS().get(j);
                // 组装第一行提箱点数据
                if (i == 0) {
                    // 提箱地点
                    pickupNameCell = row.createCell(beginCol);
                    pickupNameAds = new CellRangeAddress(beginRow, beginRow, beginCol, ads1.getLastColumn() + containerDTO.getRightColSumCount() / 2);
                    System.out.println("第"+beginRow+"行地点："+ beginRow +","+ beginRow+"," +beginCol +","+ (ads1.getLastColumn() +containerDTO.getRightColSumCount() / 2));


                    // 提箱数量
                    pickupValCell = row.createCell(pickupNameAds.getLastColumn() + 1);
                    pickupValAds = new CellRangeAddress(beginRow, beginRow, pickupNameAds.getLastColumn() + 1, pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2);
                    System.out.println("第"+beginRow+"行数量："+beginRow +","+ beginRow  +","+ (pickupNameAds.getLastColumn() + 1)  +","+(pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2));

                } else {
                    // 提箱地点
                    pickupNameCell = row.createCell(beginCol);
                    pickupNameAds = new CellRangeAddress(beginRow, beginRow, beginCol, beginCol + containerDTO.getRightColSumCount() / 2 - 1);
                    System.out.println("第"+beginRow+"行地点："+ beginRow +","+beginRow +","+ beginCol  +","+(beginCol + containerDTO.getRightColSumCount() / 2 - 1));


                    // 提箱数量
                    pickupValCell = row.createCell(pickupNameAds.getLastColumn() + 1);
                    pickupValAds = new CellRangeAddress(beginRow, beginRow, pickupNameAds.getLastColumn() + 1, pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2);
                    System.out.println("第"+beginRow+"行数量："+beginRow +","+ beginRow +","+ (pickupNameAds.getLastColumn() + 1)  +","+(pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2));

                }
                sheet.addMergedRegion(pickupNameAds);
                pickupNameCell.setCellValue(pickUpDTO.getRegion());
                pickupNameCell.setCellStyle(style);

                sheet.addMergedRegion(pickupValAds);
                pickupValCell.setCellValue(pickUpDTO.getSize());
                pickupValCell.setCellStyle(style);
            }

            // 记录当前最大的列起始下标
            beginCol += containerDTO.getRightColSumCount();

        }
    }









    @Test
    public void partExpor111t() throws IOException {
        // 初始化测试数据
        ContractDTO data = initData();
        //1.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //2.创一个sheet 工作表
        XSSFSheet sheet = workbook.createSheet();
        // 左侧标题样式
        XSSFCellStyle style = getAndSetXSSFCellStyle(workbook);
        // 定义起始列
        int rowIndex = 0;

        Row row = sheet.createRow(rowIndex);
        Row row1 = sheet.createRow(1);

    }



    @Test
    public void partExport() throws IOException {
        // 初始化测试数据
        ContractDTO data = initData();
        //1.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //2.创一个sheet 工作表
        XSSFSheet sheet = workbook.createSheet();
        // 左侧标题样式
        XSSFCellStyle style = getAndSetXSSFCellStyle(workbook);
        // 定义起始列
        int rowIndex = 0;


        int lastColumn=1;

        // 获取当前提箱地点集合大小，作为当前总行数
        int rowMaxCount = data.getPickupMaxRowCount();
        //问题   先
        Row row = sheet.createRow(rowIndex);
        Cell cell1 = row.createCell(0);

        CellRangeAddress ads1 = new CellRangeAddress(rowIndex, rowIndex + rowMaxCount - 1, leftTitleColBeginIndex, leftTitleColEndIndex);
        System.out.println(ads1.getLastColumn());
        sheet.addMergedRegion(ads1);
        cell1.setCellValue("Pick-up Location");
        cell1.setCellStyle(style);



        /**
         * 组装提箱地点 & 提箱量
         */
        Cell pickupNameCell = null;
        CellRangeAddress pickupNameAds = null;
        Cell pickupValCell = null;
        CellRangeAddress pickupValAds = null;
        ContainerDTO containerDTO = null;
        PickUpDTO pickUpDTO = null;

        // 当前起始列下标
        int beginCol = lastColumn + 1;

            // 遍历出所有箱型  3个箱型下的数据
            for (int i = 0, len = data.getContainers().size(); i < len; i++) {
                containerDTO = data.getContainers().get(i);
                Row row1 = sheet.createRow(rowIndex);
                // 遍历出每个提箱地点
                for (int j = 0, len2 = containerDTO.getPickupSize(); j < len2; j++) {
                    // 提箱地点和提箱数量 起始行 = rowIndex + j

                    int beginRow = rowIndex + j;

                    pickUpDTO = containerDTO.getPickUpDTOS().get(j);

                    // 组装第一行提箱点数据
                    if (i == 0) {
                        // 提箱地点
                        pickupNameCell = row1.createCell(beginCol);
                        pickupNameAds = new CellRangeAddress(beginRow, beginRow, beginCol, lastColumn + containerDTO.getRightColSumCount() / 2);

                        System.out.println("第"+beginRow+"行地点："+ beginRow +","+ beginRow+"," +beginCol +","+ (lastColumn +containerDTO.getRightColSumCount() / 2));
                        // 提箱数量
                        pickupValCell = row1.createCell(pickupNameAds.getLastColumn() + 1);
                        pickupValAds = new CellRangeAddress(beginRow, beginRow, pickupNameAds.getLastColumn() + 1, pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2);
                        System.out.println("第"+beginRow+"行数量："+beginRow +","+ beginRow  +","+ (pickupNameAds.getLastColumn() + 1)  +","+(pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2));

                    } else {
                        // 提箱地点
                        pickupNameCell = row1.createCell(beginCol);
                        pickupNameAds = new CellRangeAddress(beginRow, beginRow, beginCol, beginCol + containerDTO.getRightColSumCount() / 2 - 1);
                        System.out.println("第"+beginRow+"行地点："+ beginRow +","+beginRow +","+ beginCol  +","+(beginCol + containerDTO.getRightColSumCount() / 2 - 1));

                        // 提箱数量
                        pickupValCell = row1.createCell(pickupNameAds.getLastColumn() + 1);
                        pickupValAds = new CellRangeAddress(beginRow, beginRow, pickupNameAds.getLastColumn() + 1, pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2);
                        System.out.println("第"+beginRow+"行数量："+beginRow +","+ beginRow +","+ (pickupNameAds.getLastColumn() + 1)  +","+(pickupNameAds.getLastColumn() + containerDTO.getRightColSumCount() / 2));
                    }

                    //System.out.println("地址"+len+"列"+"坐标:"+ JSON.toJSONString(pickupValCell));

                    sheet.addMergedRegion(pickupNameAds);
                    pickupNameCell.setCellValue(pickUpDTO.getRegion());
                    pickupNameCell.setCellStyle(style);

                    //System.out.println("数量"+len+"列"+"坐标:"+ JSON.toJSONString(pickupValAds));
                    System.out.println(pickupValAds.toString());
                    sheet.addMergedRegion(pickupValAds);
                    pickupValCell.setCellValue(pickUpDTO.getSize());
                    pickupValCell.setCellStyle(style);
                }

                // 记录当前最大的列起始下标
                beginCol += containerDTO.getRightColSumCount();
            }





        FileOutputStream out = new FileOutputStream(path + "/导出" + System.currentTimeMillis() + ".xlsx");
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();


    }



    private XSSFCellStyle getLeftTitleCellStyle(XSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = sxssfWorkbook.createCellStyle();
        // 自动换行
        xssfCellStyle.setWrapText(true);
        // 设置水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 设置垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return xssfCellStyle;
    }

    private XSSFCellStyle getAndSetXSSFCellStyle(XSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = sxssfWorkbook.createCellStyle();
        // 自动换行
        xssfCellStyle.setWrapText(true);
        // 设置水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return xssfCellStyle;
    }

    private XSSFCellStyle getAndSetXSSFCellStyleHeader(XSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = sxssfWorkbook.createCellStyle();
        Font font = sxssfWorkbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 14);
        // 字体粗细
        font.setBold(true);
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 背景颜色
        xssfCellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return xssfCellStyle;
    }

}
